<?php

/* Database info */
define('DBName', 'AdCo');
define('DBURL', 'localhost');
define('DBusername', 'root');
define('DBpassword', '');

define('userTable', 'users');
define('fileTable', 'files');
define('rightsTable', 'rights');

define('DBlog', 'DataBaseQuery.log');

/* Type of users. A new user must be of one of these types. */
$userTypes = array("Admin", "Mod", "User");

$validPermissions = array(
    "read" => 0,
    "write" => 1,
    "settings" => 2,
);


/**
 * Logs a message.
 * @param type $message 
 */
function dblog($message) {
    error_log($message . "<\n", 3, DBlog);
}

/**
 * Gets the string representation of the rights.
 * @global array $validPermissions
 * @param int $right the int representation of the right
 * @return string the string representation of the right
 */
function interpretRights($right) {
    global $validPermissions;
    return array_search($right, $validPermissions);
}

/* User table operations. */

/**
 * Inserts a user into the database.
 * @param string $user the username
 * @param string $password the password asociated
 * @param string $type something from the set userTypes
 * @return bool if the insert failed or not
 */
function insertUser($user, $password, $type = "User") {
    $inserted = false;
    global $userTypes;

    if (!in_array($type, $userTypes))
        return false;

    if (userExists($user))
        return false;

    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "INSERT INTO " .
            userTable .
            "(username, password, userType) VALUES" . '("' .
            $user .
            '", "' .
            $password .
            '", "' .
            $type .
            '")';

    $inserted = $connection->query($query);
    $connection->close();

    if (!$inserted)
        dblog("Insert failed. Query=" . $query);

    return $inserted;
}

/**
 * Checks if a user exists in the userTable.
 * @param string $username the username to search for
 * @return string the password of the user
 */
function userExists($username) {
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "SELECT password FROM " .
            userTable .
            " WHERE username=\"" .
            $username .
            '"';

    $response = $connection->query($query);
    $connection->close();

    $result = $response->fetch_assoc();
    $ret = $result["password"];
    $response->free();

    return $ret;
}

/**
 * Gets a username based on a user id.
 * @param int $user_id the user id to be searched
 * @return string the username associated
 */
function getUserName($user_id) {
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "SELECT username FROM " .
            userTable .
            " WHERE idusers = " .
            $user_id;

    $response = $connection->query($query);
    $connection->close();

    if ($response == null)
        return null;
    $result = $response->fetch_assoc();
    $ret = $result["username"];
    $response->free();

    return $ret;
}

/**
 * Gets the user id based on a username.
 * @param string/int $user the username to search
 * @return int the id of the user
 */
function getUserId($user) {

    if (is_int($user))
        return $user;

    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "SELECT idusers FROM " .
            userTable .
            " WHERE username = " .
            '"' .
            $user .
            '"';
    $response = $connection->query($query);
    $connection->close();

    $result = $response->fetch_assoc();
    $ret = $result["idusers"];
    $response->free();

    return $ret;
}

/**
 * Gets the users list.
 * @return array key is user ids, value is username
 */
function getUsersList() {
    $list = array();
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);
    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "SELECT idusers, username FROM " .
            userTable;
    $response = $connection->query($query);
    $connection->close();
    if ($response == null)
        return null;
    while ($result = $response->fetch_assoc()) {
        $list[$result["idusers"]] = $result["username"];
    }
    $response->free();

    return $list;
}

/**
 * Gets the password for a user.
 * @param string $username the user to search for
 * @return string the password of the user
 */
function getUserPassword($username) {
    return userExists($username);
}

/* File table operations. */

/**
 * Retrieve the fileid of a file.
 * @param string $filename the filename to search for
 * @param int $ownerid the creator of the file
 * @return int the fileid
 */
function getFileId($filename, $ownerid) {
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "SELECT idfiles FROM " .
            fileTable .
            " WHERE filename = " .
            '"' .
            $filename .
            '" AND idCreator = ' .
            $ownerid;

    $response = $connection->query($query);
    $connection->close();

    $result = $response->fetch_assoc();
    $ret = $result["idfiles"];
    $response->free();

    return $ret;
}

function getFileId2($filename) {
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "SELECT idfiles FROM " .
            fileTable .
            " WHERE filename = " .
            '"' .
            $filename .
            '"';

    $response = $connection->query($query);
    $connection->close();

    $result = $response->fetch_assoc();
    $ret = $result["idfiles"];
    $response->free();

    return $ret;
}

/**
 * Gets the filename for a specific file id.
 * @param int $file_id the id to search for
 * @return string the filename associated
 */
function getFileName($file_id) {
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "SELECT filename FROM " .
            fileTable .
            " WHERE idfiles = " .
            $file_id;

    $response = $connection->query($query);
    $connection->close();

    $result = $response->fetch_assoc();
    $ret = $result["filename"];
    $response->free();

    return $ret;
}

/**
 * Gets the owner of a file.
 * @param int $fileid the PK of the file
 * @return int the userid of the owner
 */
function getFileOwner($fileid) {
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "SELECT idCreator FROM " .
            fileTable .
            " WHERE idfiles = " .
            $fileid;

    $response = $connection->query($query);
    $connection->close();
    if ($response == null)
        return null;

    $result = $response->fetch_assoc();
    $ret = $result["idCreator"];
    $response->free();

    return $ret;
}

/**
 * Returns the properties of the file.
 * @param int $fileid the files id
 * @return array first value is the userid, second the filename
 */
function getFileProperties($fileid) {
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "SELECT idCreator, filename FROM " .
            fileTable .
            " WHERE idfiles = " .
            $fileid;

    $response = $connection->query($query);
    $connection->close();
    if ($response == null)
        return null;

    $result = $response->fetch_assoc();

    $idCreator = $result["idCreator"];
    $filename = $result["filename"];

    $response->free();

    return array($idCreator, $filename);
}

/**
 * Gets all the files that the user has been granted access to.
 * @param string $username the user in question
 * @return array list of the id files
 */
function listPersonalFiles($username) {
    $list = array();

    $userid = getUserId($username);
    if ($userid == null)
        return null;

    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "SELECT idfiles, filename FROM " .
            fileTable .
            " WHERE idCreator = " .
            $userid;

    $response = $connection->query($query);
    $connection->close();

    if ($response == null)
        return null;
    while ($result = $response->fetch_assoc()) {
        $list[] = $result["idfiles"];
    }
    $response->free();

    return $list;
}

/**
 * Checks if a file is registered in the table.
 * @param string $filename the files name
 * @param int $ownerid the owner of the file
 * @return null/int the file id
 */
function fileExists($filename, $ownerid) {
    return getFileId($filename, $ownerid);
}

/**
 * Inserts a file into the database
 * @param string $filename the FS name of the file
 * @param int $creatorid the owner
 * @return bool if the insert succeded
 */
function insertNewFile($filename, $creatorid) {

    if (fileExists($filename, $creatorid))
        return false;

    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "INSERT INTO " .
            fileTable .
            "(`filename`, `idCreator`) VALUES ('" .
            $filename .
            "', '" .
            $creatorid .
            "')";
    $response = $connection->query($query);
    $connection->close();

    return $response;
}

/**
 * Remvoes a file from the database.
 * @param int $fileid the file's id.
 * @return bool if the operation succeded
 */
function removeFile($fileid) {
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "DELETE FROM " .
            fileTable .
            "WHERE idfiles = " .
            $fileid;
    $response = $connection->query($query);
    $connection->close();

    return $response;
}

/* Rights table operations. */

/**
 * Gets the files that a user has been granted access to.
 * @param int $user_id the user's id
 * @return array listof the id files
 */
function listVisibleFiles($user_id) {
    $visible = array();
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "SELECT idFile FROM " .
            rightsTable .
            " WHERE idUser = " .
            $user_id;

    $response = $connection->query($query);
    $connection->close();

    if ($response == null)
        return null;
    while ($result = $response->fetch_assoc()) {
        $visible[] = $result["idFile"];
    }
    $response->free();

    return $visible;
}

/**
 * Gets all the usrs who have any type of permission on the file.
 * @param int $file_id the file in question
 * @return list indexed by the userid and valued with permision
 */
function getGlobalFileRights($file_id) {
    $list = array();
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "SELECT idUser, permision FROM " .
            rightsTable .
            " WHERE idFile = " .
            $file_id;

    $response = $connection->query($query);
    $connection->close();
    if ($response == null)
        return null;

    while ($result = $response->fetch_assoc()) {
        $list[$result["idUser"]] = $result["permision"];
    }
    $response->free();

    return $list;
}

/**
 * Retrieve the rights that a user has for a file.
 * @param int $file_id the id of the file
 * @param int $user_id the id of the required file
 * @return string to be changed
 */
function getFileRights($file_id, $user_id) {
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "SELECT permision FROM " .
            rightsTable .
            " WHERE idFile = " .
            $file_id .
            " AND idUser = " .
            $user_id;
    $response = $connection->query($query);
    $connection->close();
    if ($response == null)
        return null; // -1

    $result = $response->fetch_assoc();
    $rights = $result["permision"];
    $response->free();

    if ($rights == null)
        return null;

    switch ($rights) {
        case 0:
            $rights = "read";
            break;
        case 1:
            $rights = "read/write";
            break;
        case 2:
            $rights = "read/write/edit_settings";
            break;

        default:
            $rights = "none";
            break;
    }

    return $rights;
}

/**
 * Gives a user rights to access a file.
 * @param int $fileid the file to be accessed
 * @param int $userid the user's id
 * @param int $rights a value decribing the permission
 * @return bool if the insert succeded
 */
function addPermission($fileid, $userid, $rights) {
    global $validPermissions;
    if (!in_array($rights, $validPermissions))
        return false;

    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "INSERT INTO " .
            rightsTable .
            "(`idFile`, `idUser`, `permision`) VALUES ('" .
            $fileid .
            "', '" .
            $userid .
            "', '" .
            $rights .
            "')";
    $response = $connection->query($query);
    $connection->close();

    if (!$response)
        dblog("Insert failed. Query=" . $query);

    return $response;
}

/**
 * Modifies the existing permisions that a user has for a file.
 * @param int $fileid the files id
 * @param int $userid the users id
 * @param int $rights the new rights
 * @return bool true or false
 */
function modifyPermissions($fileid, $userid, $rights) {
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "UPDATE " .
            rightsTable .
            " SET permision = " .
            $rights .
            " WHERE idUser = " .
            $userid .
            " AND idFile = " .
            $fileid;
    $response = $connection->query($query);
    $connection->close();

    if (!$response)
        falog("Update failed. Query=" . $query);

    return $response;
}

/**
 * Removes any permission that a user has over a file.
 * @param int $fileid The files id.
 * @param int $userid The users id.
 * @return bool Returns true on success or false on failure.
 */
function removePermissions($fileid, $userid) {
    $connection = new mysqli(DBURL, DBusername, DBpassword, DBName);

    if ($connection->connect_errno) {
        dblog("Connect failed: " . $connection->connect_error);
        exit();
    }

    $query = "DELETE FROM " .
            rightsTable .
            " WHERE idUser = " .
            $userid .
            " AND idFile = " .
            $fileid;

    $response = $connection->query($query);
    $connection->close();

    if (!$response)
        falog("Delete failed. Query=" . $query);

    return $response;
}

/**
 * Sets the permissions for a file and user.
 * First tests if the user already has permissions.
 * @param int $fileid the file's id to be added
 * @param int $userid the user to be given rights
 * @param int $rights the rights
 * @return bool if the operations succeds
 */
function setPermissions($fileid, $userid, $rights) {
    if ($rights == -1) {
        return removePermissions($fileid, $userid);
    }
    if (getFileRights($fileid, $userid)) {
        return modifyPermissions($fileid, $userid, $rights);
    } else {
        return addPermission($fileid, $userid, $rights);
    }
}

?>
